USE [SIVN_OnlineTestingEngine]
GO
/****** Object:  StoredProcedure [testing].[prc_GetPublicUserQuestionGroups]    Script Date: 07/04/2012 20:42:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*-------------------------------------------------------------------------------------
** Name:	prc_GetPublicUserQuestionGroups
** Purpose:	Get all question groups are post by user and friend
** 
** ------------------------------------------------------------------------------------
** Input Parameters: 
** @UserId
** @PageSize
** @PageNum
** N/A
** 
** ------------------------------------------------------------------------------------
** Usage
** EXEC testing.prc_GetPublicUserQuestionGroups 1, 5, 1
**
** ------------------------------------------------------------------------------------
** Author:    Hau
** Date:      2012-07-04
** Database:  SIVN_OnlineTestingEngine
**
** ------------------------------------------------------------------------------------
** Modifications:
** 2012-07-02	Hau		Create
** ----------------------------------------------------------------------------------*/

CREATE PROCEDURE [testing].[prc_GetPublicUserQuestionGroups]
@UserId int,
@PageSize int,
@PageNum int
AS
BEGIN
	SET NOCOUNT ON;
	
	
	WITH QGsRN AS
	(
		-- get private
		SELECT ROW_NUMBER() OVER(ORDER BY qg.ModifiedDate, qg.Id) AS RowNum
			,qg.Id, qg.Name, qg.GroupId, qg.UserId, qg.CategoryId, qg.Time, qg.Image, 
			qg.CreatedDate, qg.CreatedBy, qg.ModifiedDate, qg.ModifiedBy,
			(select COUNT(Id) from testing.[Like] where QuestionGroupId = qg.Id and IsDislike = 'false') 'Likes',
			(select COUNT(Id) from testing.[Like] where QuestionGroupId = qg.Id and IsDislike = 'true') 'DisLikes',
			(select COUNT(Id) from testing.QuestionGroupDetail where QuestionGroupId = qg.Id) 'TotalQuestion'
		FROM testing.QuestionGroup qg
		where qg.UserId = @UserId
		
		-- get friend
		union
		SELECT ROW_NUMBER() OVER(ORDER BY qg.ModifiedDate, qg.Id) AS RowNum
			,qg.Id, qg.Name, qg.GroupId, qg.UserId, qg.CategoryId, qg.Time, qg.Image, 
			qg.CreatedDate, qg.CreatedBy, qg.ModifiedDate, qg.ModifiedBy,
			(select COUNT(Id) from testing.[Like] where QuestionGroupId = qg.Id and IsDislike = 'false') 'Likes',
			(select COUNT(Id) from testing.[Like] where QuestionGroupId = qg.Id and IsDislike = 'true') 'DisLikes',
			(select COUNT(Id) from testing.QuestionGroupDetail where QuestionGroupId = qg.Id) 'TotalQuestion'
		from testing.QuestionGroup qg join testing.QuestionGroupShareMode qs on qg.Id = qs.QuestionGroup_Id
		where qg.UserId in 
			(select Friend_Id 
			from SIVN_Security.security.Friend
			where [User_Id] = @UserId)
		and (qs.ShareMode_Id = 3 or
			(qs.ShareMode_Id = 4 and @UserId in 
				(select [User_Id] from testing.QuestionGroupCustomShare 
				where QuestionGroupShareMode_Id = qs.Id)
			)
			)
	   
		-- get group
		union
		SELECT ROW_NUMBER() OVER(ORDER BY qg.ModifiedDate, qg.Id) AS RowNum
			,qg.Id, qg.Name, qg.GroupId, qg.UserId, qg.CategoryId, qg.Time, qg.Image, 
			qg.CreatedDate, qg.CreatedBy, qg.ModifiedDate, qg.ModifiedBy,
			(select COUNT(Id) from testing.[Like] where QuestionGroupId = qg.Id and IsDislike = 'false') 'Likes',
			(select COUNT(Id) from testing.[Like] where QuestionGroupId = qg.Id and IsDislike = 'true') 'DisLikes',
			(select COUNT(Id) from testing.QuestionGroupDetail where QuestionGroupId = qg.Id) 'TotalQuestion'
		from testing.QuestionGroup qg
		where GroupId in
			(select gm.GroupId
			from SIVN_Security.security.GroupMember gm 
			where gm.UserId = @UserId)
			
		-- get public
		union
		SELECT ROW_NUMBER() OVER(ORDER BY qg.ModifiedDate, qg.Id) AS RowNum
			,qg.Id, qg.Name, qg.GroupId, qg.UserId, qg.CategoryId, qg.Time, qg.Image, 
			qg.CreatedDate, qg.CreatedBy, qg.ModifiedDate, qg.ModifiedBy,
			(select COUNT(Id) from testing.[Like] where QuestionGroupId = qg.Id and IsDislike = 'false') 'Likes',
			(select COUNT(Id) from testing.[Like] where QuestionGroupId = qg.Id and IsDislike = 'true') 'DisLikes',
			(select COUNT(Id) from testing.QuestionGroupDetail where QuestionGroupId = qg.Id) 'TotalQuestion'
		from testing.QuestionGroup qg join testing.QuestionGroupShareMode qs on qg.Id = qs.QuestionGroup_Id
		where qs.ShareMode_Id = 1
	)
	
	SELECT * FROM QGsRN
	WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize
	ORDER BY ModifiedDate, Id
	
	SELECT @@Identity              
  
END

